/**
 * AddTakenCourseDAO class.
 */
package pits.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pits.dto.AddTakenCourseDTO;
import pits.form.AddTakenCourseForm;
import pits.util.Logger;

public class AddTakenCourseDAO {

	public AddTakenCourseDTO getData() {
		AddTakenCourseDTO result = new AddTakenCourseDTO();
		List<String> companyNames = new ArrayList<String>();
		List<Integer> companyIDs = new ArrayList<Integer>();

		List<List<String>> departNames = new ArrayList<List<String>>();
		List<List<Integer>> departIDs = new ArrayList<List<Integer>>();

		List<String> eachComDepNames = null;
		List<Integer> eachComDepIDs = null;

		List<List<List<String>>> courseNames = new ArrayList<List<List<String>>>();
		List<List<List<Integer>>> courseIDs = new ArrayList<List<List<Integer>>>();

		List<List<String>> eachComCourseNames = null;
		List<List<Integer>> eachComCourseIDs = null;

		List<String> eachDepCourseNames = null;
		List<Integer> eachDepCourseIDs = null;

		Connection conn = null;
		String query = null;
		PreparedStatement sqlStatement = null;
		ResultSet comResultSet = null;
		ResultSet departResultSet = null;
		ResultSet courseResultSet = null;

		try {
			query = "SELECT * FROM company WHERE isDeleted=0";

			conn = pits.util.Connection.getConnection();
			sqlStatement = conn.prepareStatement(query);

			// new Logger().write(query);

			comResultSet = sqlStatement.executeQuery();

			while (comResultSet.next()) {
				query = "SELECT * FROM department" + " WHERE CompanyID=" + comResultSet.getInt("CompanyID")
				        + " AND isDeleted=0";

				// new Logger().write(query);

				sqlStatement = conn.prepareStatement(query);
				departResultSet = sqlStatement.executeQuery();

				eachComDepNames = new ArrayList<String>();
				eachComDepIDs = new ArrayList<Integer>();
				eachComCourseNames = new ArrayList<List<String>>();
				eachComCourseIDs = new ArrayList<List<Integer>>();

				while (departResultSet.next()) {
					// new
					// Logger().write(departResultSet.getString("DepartName"));
					eachComDepNames.add(departResultSet.getString("DepartName"));
					eachComDepIDs.add(departResultSet.getInt("DepartID"));

					query = "SELECT * FROM course " + "WHERE DepartID=" + departResultSet.getInt("DepartID") + " "
					        + "AND isDeleted=0";
					// new pits.util.Logger().write(query);

					sqlStatement = conn.prepareStatement(query);
					courseResultSet = sqlStatement.executeQuery();

					eachDepCourseNames = new ArrayList<String>();
					eachDepCourseIDs = new ArrayList<Integer>();

					while (courseResultSet.next()) {
						// new
						// Logger().write(courseResultSet.getString("Name"));
						eachDepCourseNames.add(courseResultSet.getString("Name"));
						eachDepCourseIDs.add(courseResultSet.getInt("CourseID"));
					}

					eachComCourseNames.add(eachDepCourseNames);
					eachComCourseIDs.add(eachDepCourseIDs);

					query = "SELECT * FROM project " + "WHERE DepartID=" + departResultSet.getInt("DepartID") + " "
					        + "AND isDeleted=0";
					// new pits.util.Logger().write(query);

					sqlStatement = conn.prepareStatement(query);

				}
				// new Logger().write(comResultSet.getString("ComName"));
				companyNames.add(comResultSet.getString("ComName"));
				companyIDs.add(comResultSet.getInt("CompanyID"));
				departNames.add(eachComDepNames);
				departIDs.add(eachComDepIDs);
				courseNames.add(eachComCourseNames);
				courseIDs.add(eachComCourseIDs);
			}

			// new Logger().writeln("getCommonData: done");

		} catch (SQLException sqlEx) {
			new Logger().writeln(sqlEx.toString());
		} finally {
			try {
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new Logger().writeln(sqlEx.toString());
			}
		}
		result.setCompanyNames(companyNames);
		result.setCompanyIDs(companyIDs);
		result.setDepartNames(departNames);
		result.setDepartIDs(departIDs);
		result.setCourseNames(courseNames);
		result.setCourseIDs(courseIDs);

		// new Logger().writeln("getData: done");

		return result;
	}

	public void update(AddTakenCourseForm formAddTakenCourse) {

		Connection conn = null;
		String query = null;
		PreparedStatement sqlStatement = null;

		try {
			conn = pits.util.Connection.getConnection();

			query = "INSERT IGNORE courseexp " + "VALUES (" + formAddTakenCourse.getCourseID() + ", "
			        + formAddTakenCourse.getEmployeeID() + ", " + "0, " + "'" + formAddTakenCourse.getGrade() + "'"
			        + ")";
			new pits.util.Logger().writeln(query);
			sqlStatement = conn.prepareStatement(query);
			sqlStatement.executeUpdate();
		} catch (SQLException sqlEx) {
			new pits.util.Logger().writeln(sqlEx.toString());
		} finally {
			try {
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new pits.util.Logger().writeln(sqlEx.toString());
			}
		}
	}
}
